package com.cy.pj.notice.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository //此注解一般用于描述数据访问对象的具体实现类
public class NoticeJdbcDaoimpl接口实现类 implements NoticeJdbcDao {
    @Autowired
    private DataSource dataSource;

    @Override
    public int insertNotice(Map<String, Object> map) {
        return 0;
    }

    @Override
    public List<Map<String, Object>> selectNotices() throws SQLException {
        //1,获取连接
        Connection conn = dataSource.getConnection();
        //2,创建PreparedStatement
        //***注意的教训：要在from前面加个“空格”，和上一句隔开来，不然会报错
        String sql = "select id,title,content,status,type,createdTime" +
                " from sys_notices where id>=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //3，发送sql语句，并返回结果集
        pstmt.setInt(1, 2);
        //ResultSet rs=pstmt.execute();
        boolean flag = pstmt.execute();
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<>();
        if (flag) {
            rs = pstmt.getResultSet(); //返回的查询结果集
            ResultSetMetaData rsmd = rs.getMetaData();//获取结果集对应的的元数据--用来获取字段名和表名
            int columnCount = rsmd.getColumnCount(); //结果集有几列--6列(id,title,content,status,type,createdTime)

            while (rs.next()) {    //行映射，循环一次取一行，一行记录一个map对象
                Map<String, Object> map = new HashMap<>();
                for (int i = 2; i <= columnCount; i++) {  //循环一次取一行结果的值
                    String b = rsmd.getColumnLabel(i);//for循环取每列的--字段别名(没有别名就是默认的别名)
                    map.put(b, rs.getObject(b));
                }
                /*map.put("id",rs.getInt("id"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("status",rs.getString("status"));
                map.put("type",rs.getString("type"));
                map.put("createdTime",rs.getTimestamp("createdTime"));
                */
                list.add(map);
            }
        }
        rs.close();
        pstmt.close();
        conn.close();
        return list;

    }
}
